﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace _201206QuanLyBanHang
{
    class SQLCommand
    {
        DataConn mc = new DataConn();

        #region Cau lenh sql 2
        // Đưa file minhconnect về chung với file dataconnect
        
        KhachHangMuaSP khmsp = new KhachHangMuaSP();
        //Lay danh sach Sản phẩm
        public DataTable LayDanhSachSanPham()
        {
            String sql = "select masp, tensp from SanPham";
            return mc.GetDataTable(sql);
        }
        //Lay danh sach nhan vien
        public DataTable LayDanhSachNhanVien()
        {
            String sql = "select MaNhanVien, (honhanvien + ' ' + TenNhanVien) as HoTenNhanVien from NhanVien where tinhtrangnv=1";
            return mc.GetDataTable(sql);
        }
        //Lay danh sach khu vuc
        public DataTable LayDanhSachKhuVuc()
        {
            String sql = "Select MaKhuVuc, TenKhuVuc from khuvuc order by TenKhuVuc";
            return mc.GetDataTable(sql);
        }
        //Lay danh sach khach hang
        public DataTable LayDanhSachKhachHang(String makhuvuc)
        {
            String sql = "select MaKH, TenKH, TenCuaHang, DiaChiKH from KhachHang where makhuvuc=" + makhuvuc + " and tinhtrangkh=1 order by TenCuaHang";
            return mc.GetDataTable(sql);
        }
        //Lay danh sach khach hang cho List cập nhật nhiều
        public DataTable LayDanhSachKhachHang_KhuVuc(String makhuvuc)
        {
            String sql = "select MaKH, TenCuaHang, SanPham from KhachHang where makhuvuc=" + makhuvuc + " and tinhtrangkh=1 order by TenCuaHang";
            return mc.GetDataTable(sql);
        }
        //Hien thi chi tiết mua sản phẩm của khách hàng A
        public DataTable LayThongTinMua(String makhachhang)
        {
            String sql = "Select sp.masp, convert(varchar, kh.ngaymua, 103) as ngaymua, sp.TenSP, kh.soluongmua, kh.ghichu_KHMSP from KH_Mua_SP as kh, SanPham as sp where kh.masp=sp.masp and makh=" + makhachhang + " order by ngaymua desc";
            return mc.GetDataTable(sql);
        }
        //Hien thi thông tin Tìm kiếm theo Khu vực và Tên khách hàng
        public DataTable LayThongtinTimkiemKVvaKH(String makhuvuc, String tenkhachhang)
        {
            // anh minh String sql = "select MaKH, TenKH, TenCuaHang, DiaChiKH from KhachHang where MaKhuVuc='" + makhuvuc + "' and TenKH like '%" + tenkhachhang + "%'";
            String sql = "select MaKH, TenKH, TenCuaHang, DiaChiKH from KhachHang where MaKhuVuc='" + makhuvuc + "' and dbo.fnChuyenKhongDau(TenKH) like '%" + tenkhachhang + "%'";
            return mc.GetDataTable(sql);
        }
        //Hien thi thông tin Tìm kiếm theo Khu vực và Tên Cửa hàng
        public DataTable LayThongtinTimkiemKVvaCH(String makhuvuc, String tencuahang)
        {
            String sql = "select MaKH, TenKH, TenCuaHang, DiaChiKH from KhachHang where MaKhuVuc='" + makhuvuc + "' and dbo.fnChuyenKhongDau(TenCuaHang) like '%" + tencuahang + "%'";
            return mc.GetDataTable(sql);
        }
        //Thêm số lượng mà khách hàng mua
        public bool ThemSoLuong(KhachHangMuaSP kh)
        {
            if (khmsp.KiemTraDuLieu(kh))
            {
                String sql = String.Format("Insert into KH_Mua_SP(MaSP, MaKH, NgayMua, MaNhanVien, SoLuongMua, Gia_SP_NgayMua, Gia_ChietKhau_NgayMua, Ghichu_KHMSP, ThanhTien)"
                    + " Values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')", kh.MaSP, kh.MaKH, kh.NgayMua, kh.MaNV,
                    kh.Soluong, kh.Gia_SP_NgayMua, kh.Gia_Chietkhau_NgayMua, kh.Ghichu, kh.ThanhTien);
                if (mc.ExecuteQuery(sql))
                {
                    return true;
                }

            }
            return false;

        }
        //Lấy Giá Sản phẩm theo ngày mua mà Khách hàng mua 
        public double LayGiaSanPham(String MaSP)
        {
            String sql = "select GiaSP from SanPham where MaSP=" + MaSP;
            return mc.TraveDongDuLieu(sql);
        }
        public double LayGiaSanPham_ChietKhau(String MaKH, String MaSP)
        {
            String sql = "select GiaChietKhau from ChietKhau where MaKH='" + MaKH + "' and MaSP=" + MaSP;
            return mc.TraveDongDuLieu(sql);
        }
        //Lấy Giá Chiết khấu theo ngày mua mà Khách hàng mua 
        public double LayGiaChietKhau(int MaKH, String MaSP)
        {
            String sql = "select GiaChietKhau from ChietKhau where MaKH=" + MaKH + " and MaSP=" + MaSP;
            return mc.TraveDongDuLieu(sql);
        }

        //Phần này thuộc về xử lý bên KhachHang_ThanhToan
        //*******************************************
        //Lấy Ngày Nợ thứ 2 (nhỏ hơn ngày mới nhất), từ ngày lấy được lấy ra số tiền nợ theo ngày đó
        public double LayNgayThanhToanNoThu2(int MaKH)
        {
            String sql = "select SoTienNo from KhachHang_ThanhToan where MaKH='" + MaKH + "' and NGAYTHANHTOAN=(  select max(ngaythanhtoan) from KhachHang_ThanhToan where MaKH='" + MaKH + "' and NGAYTHANHTOAN < (select MAX(ngaythanhtoan) from KhachHang_ThanhToan where MaKH='" + MaKH + "'))";
            return mc.TraveDongDuLieu(sql);
        }

        //Cập nhật tiền khách nợ (new)
        public bool CapnhatTienKhachNo(KhachHangMuaSP kh)
        {
            String sql = String.Format("Update KhachHang_ThanhToan Set SoTienNo='{0}', SoTienThanhToan='{1}', GhiChu='{2}' where MaKH='{3}' and NgayThanhToan='{4}' ", kh.SoTienNo, kh.SoTienThanhToan, kh.Ghichu, kh.MaKH, kh.NgayThanhToan);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;
        }

        //Thêm số tiền khách nợ
        public bool LuuSoTienKhachNo(KhachHangMuaSP kh)
        {
            String sql = String.Format("Insert into KhachHang_ThanhToan(MaKH, NgayThanhToan, SoTienThanhToan, SoTienNo, GhiChu)"
                + " Values('{0}', '{1}', '{2}', '{3}', '{4}')", kh.MaKH, kh.NgayThanhToan, kh.SoTienThanhToan, kh.SoTienNo, kh.Ghichu);
            if (mc.ExecuteQuery(sql))
            {
                MessageBox.Show("Thêm thành công");
                return true;
            }
            return false;
        }

        //Tính tổng tiền ngày mới nhất trên các loại SP, dựa trên bảng KH_Mua_SP
        public double TongTienKhachNoNgayMuaMoiNhat(int MaKH)
        {
            String sql = "select SUM(thanhtien) from KH_MUA_SP where MAKH=" + MaKH + " and NGAYMUA=(select max(NGAYMUA) from KH_MUA_SP where MAKH=" + MaKH + ") Group by MaKH, NgayMua";
            return mc.TraveDongDuLieu(sql);
        }
        //Select ra Tiền nợ theo ngày Thanh toán mới nhất
        public double TienKhachNoNgayThanhToanMoiNhat(int MaKH)
        {
            String sql = "select SoTienNo from KhachHang_ThanhToan where MAKH=" + MaKH + " and NGAYThanhToan=(select max(NGAYThanhToan) from KhachHang_ThanhToan where MAKH=" + MaKH + ")";
            return mc.TraveDongDuLieu(sql);
        }
        //Lấy tiền nợ mà Khách đang nợ tại bảng KhachHang_ThanhToan
        //public double TongTienKhachNoDaCo(int MaKH) //Lấy số tiền mà Khách đang nợ có trong bảng KH_ThanhToan_No
        //{
        //    String sql = "select SUM(SoTienNo) from KhachHang_ThanhToan where MAKH=" + MaKH + " and NGAYThanhToan=(select max(NGAYMUA) from KH_MUA_SP where MAKH=" + MaKH + ")";
        //    return mc.TraveDongDuLieu(sql);
        //}

        //Lấy dòng dữ liệu từ bảng KH_ThanhToan_No. Nếu ko có trả về 0
        public double LayDongDuLieutuKH_ThanhToan_No(int MaKH)
        {
            String sql = "select count(*) from KhachHang_ThanhToan where MaKH=" + MaKH;
            return mc.TraveDongDuLieu(sql);
        }
        //Lấy dòng dữ liệu từ bảng KH_ThanhToan_No. Nếu là ngày mua mới thì kết quả trả trả về 0 còn cùng ngày thì là 1
        public double LayDongDuLieutuKH_ThanhToan_No_TheoMaKHvaNgayMua(int MaKH, DateTime NgayMua)
        {
            String sql = "select count(*) from KhachHang_ThanhToan where MaKH='" + MaKH + "' and NgayThanhToan='" + NgayMua + "'";
            return mc.TraveDongDuLieu(sql);
        }
        //Lấy số tiền nợ của Khác hàng theo ngày mới nhất
        public double LayTienNo(String MaKH)
        {
            String sql = "select convert(varchar, cast(sotienno as money), 1) as sotienno from KhachHang_ThanhToan where makh='" + MaKH + "' and ngaythanhtoan=(select MAX(ngaythanhtoan) from KhachHang_ThanhToan where makh='" + MaKH + "')";
            return mc.TraveDongDuLieu(sql);
        }
        public String LayTienNoString(String MaKH)
        {
            String sql = "select replace(cast(convert(varchar, cast(sotienno as money), 1) as varchar),'.00','') as sotienno from KhachHang_ThanhToan where makh='" + MaKH + "' and ngaythanhtoan=(select MAX(ngaythanhtoan) from KhachHang_ThanhToan where makh='" + MaKH + "')";
            return mc.TraveDongDuLieuString(sql);
        }

        //Lấy Tiền Khách đã trả trước = ngày hiện tại
        public double LayTienKhachThanhToanTruoc(String MaKH, DateTime datetime)
        {
            String sql = "select sotienthanhtoan from KhachHang_ThanhToan where makh='" + MaKH + "' and ngaythanhtoan='" + datetime + "'";
            return mc.TraveDongDuLieu(sql);
        }
        //Lưu số tiền Khách trả (tính toán lại khi Khách hàng còn nợ)
        //Tiền nợ còn lại= Tiền trả - Tiền nợ hôm trước (theo ngày mới nhất)

        //Xem số ngày mới nhất (mà khách đang nợ) so với ngày hôm nay khách trả, xem có = nhau ko, nếu ko thì Insert, ngc lại là Update
        public double KiemTraNgayNovaNgayTra(String MaKH, DateTime datetime) //=1 là ngày mua = ngày trả, =0 là # nhau.
        {
            String sql = "select count(makh) from KhachHang_ThanhToan where makh='" + MaKH + "' and NgayThanhToan='" + datetime + "'";
            return mc.TraveDongDuLieu(sql);
        }

        //***********************************************************
        //*******************************************

        //Đếm số khách theo khu vực
        public double DemSoLuongKhachTheo_KhuVuc(String MaKhuVuc)
        {
            String sql = "select count(makh) from KhachHang where MaKhuVuc='" + MaKhuVuc + "'";
            return mc.TraveDongDuLieu(sql);
        }
        //Đếm khách theo Khu vực và Tên khách hàng
        public double DemSoLuongKhachTheoKVvaKH(String makhuvuc, String tenkhachhang)
        {
            String sql = "select count(makh) from KhachHang where MaKhuVuc='" + makhuvuc + "' and TenKH like '%" + tenkhachhang + "%'";
            return mc.TraveDongDuLieu(sql);
        }
        //Đếm khách theo Khu vực và Tên Cửa hàng
        public double DemSoLuongKhachTheoKVvaCH(String makhuvuc, String tencuahang)
        {
            String sql = "select count(makh) from KhachHang where MaKhuVuc='" + makhuvuc + "' and TenCuaHang like '%" + tencuahang + "%'";
            return mc.TraveDongDuLieu(sql);
        }
        //Nhật ký Trả tiền (Thanh toán) của khách
        public DataTable NhatKyTraTien(String MaKH)
        {
            String sql = "select convert(varchar, NgayThanhToan, 103) as NgayThanhToan, replace(convert(varchar, cast(SoTienThanhToan as money), 1),'.00','') as SoTienThanhToan, GhiChuKHTT from KhachHang_ThanhToan where MaKH='" + MaKH + "' order by NgayThanhToan desc";
            return mc.GetDataTable(sql);
        }

        //Kiểm tra việc cập nhật dữ liệu theo ngày (rất quan trọng)
        //NgàyThanhToán <= Ngày hiện tại
        //NgàyThanhToán >= NgàyThanhToán(trong bảng KhachHang_ThanhToan theo ngày mới nhất)
        public String KiemTraNgayThanhToansovoiNgayHientai_khiThanhToan(DateTime NgayThanhToan)
        {
            String sql = "select DATEDIFF(dd,convert(varchar,'" + NgayThanhToan + "',101),CONVERT(varchar,getdate(),101))";
            return mc.TraveDongDuLieuString(sql);
        }
        public String KiemTraNgayThanhToansovoiNgayThanhToantrongTable_khiThanhToan(int MaKH, DateTime NgayThanhToan)
        {
            String sql = "select DATEDIFF(dd,convert(varchar,max(NgayThanhToan),101),CONVERT(varchar,'" + NgayThanhToan + "',101))  from KhachHang_ThanhToan where MaKH='" + MaKH + "'";
            return mc.TraveDongDuLieuString(sql);
        }

        //Kiểm tra việc cập nhật dữ liệu theo ngày (rất quan trọng)
        //NgàyMua <= Ngày hiện tại
        //NgàyMua >= NgàyMua(trong bảng KH_Mua_SP theo ngày mới nhất)
        public String KiemTraNgayMuasovoiNgayHientai_khiMuaSP(DateTime NgayMua)
        {
            String sql = "select DATEDIFF(dd,convert(varchar,'" + NgayMua + "',101),CONVERT(varchar,getdate(),101))";
            return mc.TraveDongDuLieuString(sql);
        }
        public String KiemTraNgayMuasovoiNgayMuatrongTable_khiMuaSP(int MaKH, DateTime NgayMua)
        {
            String sql = "select DATEDIFF(dd,convert(varchar,max(NgayMua),101),convert(varchar,'" + NgayMua + "',101))  from KH_Mua_SP where MaKH='" + MaKH + "'";
            return mc.TraveDongDuLieuString(sql);
        }

        //Kiểm tra việc Chỉnh sửa hay Xóa phải là ngày hiện tại
        public String KiemTraNgayMuaTheoChinhSuavaXoa_khiMuaSP(String NgayMua)
        {
            String sql = "select DATEDIFF(dd,convert(varchar,'" + NgayMua + "',101),CONVERT(varchar,getdate(),101))";
            return mc.TraveDongDuLieuString(sql);
        }
        //Lấy thông tin xem trong bảng KH_Mua_SP có dữ liệu hay chưa
        public double LayThongTinTuTable_KH_Mua_SP(int MaKH)
        {
            String sql = "select count(makh) from KH_Mua_SP where MaKH=" + MaKH;
            return mc.TraveDongDuLieu(sql);
        }
        //Cập nhật lại Số lượng khi Nhân viên gõ sai (chỉ xét trên ngày hiện tại)
        public bool CapnhatSoLuong(KhachHangMuaSP kh)
        {
            String sql = String.Format("Update KH_Mua_SP Set SoLuongMua='{0}', GhiChu_KHMSP='{1}', ThanhTien='{2}' where MaKH='{3}' and MaSP='{4}' and NgayMua='{5}' ", kh.Soluong, kh.Ghichu, kh.ThanhTien, kh.MaKH, kh.MaSP, kh.NgayMua);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;
        }

        public bool CapnhatSanPham_chomoiKhachHang(KhachHangMuaSP kh)//Cập nhật Sản phẩm mua lại cho mỗi Khách hàng
        {
            String sql = String.Format("Update KhachHang Set SanPham='{0}' where MaKH='{1}' ", kh.SanPham, kh.MaKH);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;
        }



        //Làm mới hoàn toàn////////////////////////////////
        ////////////////////////////////////////////////////
        /////////////////////////////////////////////////////
        /////////////////////////////////////////////////////
        public double TrangThaiKH_Thanhtoan(String MaKH) //Xết xem khách hàng có trong bảng hay ko
        {
            String sql = "select count(makh) from KhachHang_ThanhToan where MaKH='" + MaKH + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public double TongTienKH_MuaSP(String MaKH) //Tính tổng tiền Khách hàng mua sản phẩm
        {
            String sql = "select sum(ThanhTien) from KH_Mua_SP where MaKH='" + MaKH + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public double TongTienKH_ThanhToan(String MaKH)//Tính tổng tiền khách trả trong bảng Thanhtoán
        {
            String sql = "select sum(SoTienThanhToan) from KhachHang_ThanhToan where MaKH='" + MaKH + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public String ChuyenSangTien(double SoTien)
        {
            String sql = "select replace(cast(convert(varchar, cast(" + SoTien + " as money), 1) as varchar),'.00','')";
            return mc.TraveDongDuLieuString(sql);
        }

        public double KiemTraThanhToan(KhachHangMuaSP kh)//Kiểm tra nếu có dữ liệu trả về 1, o là chưa có
        {
            String sql = "select count(makh) from KhachHang_ThanhToan where MaKH=" + kh.MaKH + " and NgayThanhToan='" + kh.NgayThanhToan + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public double SelectSoTienThanhToan(KhachHangMuaSP kh)//Lấy số tiến thanh toán của ngày hôm nay
        {
            String sql = "select SoTienThanhToan from KhachHang_ThanhToan where MaKH=" + kh.MaKH + " and NgayThanhToan='" + kh.NgayThanhToan + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public void LuuSoTienKhachThanhToan(KhachHangMuaSP kh)// Hàm lưu lại số tiền khách Thanh toán
        {
            String sql = String.Format("Insert into KhachHang_ThanhToan(MaKH, NgayThanhToan, SoTienThanhToan, GhiChuKHTT, NhanVien_NhanTien)"
                + " Values('{0}', '{1}', '{2}', '{3}','{4}')", kh.MaKH, kh.NgayThanhToan, kh.SoTienThanhToan, kh.Ghichu, kh.MaNV);
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }

        }
        //Xóa dữ liệu trên bảng KH_Mua_SP
        public void XoaKhachMuaSP(String MaSP, String MaKH, String NgayMua)
        {
            String sql = "Delete from KH_Mua_SP where MaSP='" + MaSP + "' and MaKH='" + MaKH + "' and NgayMua='" + NgayMua + "'";
            if (mc.ExecuteQuery(sql))
            {
                //Success
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }
        }
        public double KiemTraMuaSP(KhachHangMuaSP kh)//Kiểm tra nếu có dữ liệu trả về 1, o là chưa có
        {
            String sql = "select count(makh) from KH_Mua_SP where MaKH=" + kh.MaKH + " and MaSP='" + kh.MaSP + "' and NgayMua='" + kh.NgayMua + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public double SelectSoLuongMuatruoccungngay(KhachHangMuaSP kh)//Lấy số lượng ngày hôm đó đã mua rồi
        {
            String sql = "select SoLuongMua from KH_Mua_SP where MaKH=" + kh.MaKH + " and MaSP='" + kh.MaSP + "' and NgayMua='" + kh.NgayMua + "'";
            return mc.TraveDongDuLieu(sql);
        }
        //Cập nhật lại khách mua hàng nhiều lần trong ngày
        public bool UpdateMuahangnhieulantrongngay(KhachHangMuaSP kh)
        {
            String sql = String.Format("Update KH_Mua_SP Set SoLuongMua='{0}', ThanhTien='{1}', GhiChu_KHMSP='{2}' where MaKH='{3}' and MaSP='{4}' and NgayMua='{5}' ", kh.Soluong,kh.ThanhTien,kh.Ghichu, kh.MaKH,kh.MaSP,kh.NgayMua);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;

        }
        //Cập nhật lại tiền Khách thanh toán
        public bool CapNhatThanhToan(KhachHangMuaSP kh)
        {
            String sql = String.Format("Update KhachHang_ThanhToan Set SoTienThanhToan='{0}', GhiChuKHTT='{1}' where MaKH='{2}' and NgayThanhToan='{3}'", kh.SoTienThanhToan, kh.Ghichu, kh.MaKH, kh.NgayThanhToan);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;

        }
        //Xóa dữ liệu trên bảng KhachHang_ThanhToan
        public void XoaNgayThanhToan(String MaKH, String NgayThanhToan)
        {
            String sql = "Delete from KhachHang_ThanhToan where MaKH='" + MaKH + "' and NgayThanhToan='" + NgayThanhToan + "'";
            if (mc.ExecuteQuery(sql))
            {
                //Success
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }
        }
        //Lấy Đơn giá theo loại của từng khách hàng
        public DataTable LayDonGia_KH(String MaKH)//Chiết khấu * Don giá
        {
            String sql = "select MaSP, GiaChietKhau from ChietKhau where MaKH=" + MaKH;
            return mc.GetDataTable(sql);
        }
        //Lấy bảng Đơn giá Sản phẩm (MaSP, Đơn giá)
        public DataTable TableDonGia()
        {
            String sql = "select MaSP, GiaSp from SanPham";
            return mc.GetDataTable(sql);
        }
        //Lấy bảng Sản phẩm (MaSP, TenSP)
        public DataTable TableMaTenSP()
        {
            String sql = "select MaSP, TenSP from SanPham";
            return mc.GetDataTable(sql);
        }
        //Lấy MaSP
        public DataTable LayMaSanPham()
        {
            String sql = "select MaSP from SanPham";
            return mc.GetDataTable(sql);
        }
        #endregion
        ///////////////////////////////////////////////////
        //Xet form qua bieu                             ///
        ///////////////////////////////////////////////////
        public DataTable LayDataQuaBieu()
        {
            String sql = "select * from tblbangdaydu";
            return mc.GetDataTable(sql);
        }
        //Loc Data tren bangdaydu
        //Loc theo nhieu dieu kien
        public DataTable LocTheoNhieuDieuKien(int MaKV, int SoTienNhoDoanhThu, int SoTienLonDoanhThu, int SoTienNhoDauTu, int SoTienLonDauTu, string NgayDau, string NgayCuoi)
        {
            String sql = "select * from tblbangdaydu where MAKHUVUC='" + MaKV + "' and convert(datetime, NGAYDT, 103)>=convert(datetime, '" + NgayDau + "', 103) and convert(datetime, NGAYDT, 103)<=convert(datetime, '" + NgayCuoi + "', 103) and  TONGDAUTU>='" + SoTienNhoDauTu + "' and TONGDAUTU<='" + SoTienLonDauTu + "' and TongDoanhThu>='" + SoTienNhoDoanhThu + "' and TongDoanhThu<='" + SoTienLonDoanhThu + "'";
            return mc.GetDataTable(sql);
        }
        //Load ComboQuaTang
        public DataTable LoadDataComboQuaTang()
        {
            String sql = "select MaQB_DL,TenQBDL from QUABIEU_DIPLE";            
            return mc.GetDataTable(sql);
        }
        //Load data vao combo khu vuc
        public DataTable LoadDataComboKhuVuc()
        {
            String sql = "select KHUVUC.MAKHUVUC,KHUVUC.TENKHUVUC from KHUVUC";
            return mc.GetDataTable(sql);
        }
        //Load data vao combo dip le
        public DataTable LoadDataComboDL()
        {
            String sql = "select  MaDipLe,TenDipLe from DIPLE";
            return mc.GetDataTable(sql);
        }
        //Load data vao cobo qua tang
        public DataTable LoadDataComboQT()
        {
            String sql = "select MaQuaBieu,TenQuaBieu from QUABIEU";
            return mc.GetDataTable(sql);
        }
        //form XetQua
        public DataTable ChonKhachHangTheoMKH(String ChuoiMAKH)
        {
            String sql = "select MAKH,TENKH,TENCUAHANG,GHICHU_KH from KHACHHANG Where MAKH IN  (";
            String Chuoi = ChuoiMAKH + ")";
            sql = sql + Chuoi;
            return mc.GetDataTable(sql);
        }
        //Tim kiem Khach hang
        public DataTable TimKiemKhachHang(String TenKhachHang)
        {
            String sql = "select * from tblbangdaydu where TENKH LIKE '" + TenKhachHang + "%'";
            return mc.GetDataTable(sql);
        }
        //Insert KH duoc nhan qua bieu
        public void ThemQuaBieuDipLe(int MaDipLe, int MaQuaBieu, string NamDL, int SoLuong, int TriGiaQB, string GhiChuKHQB)
        {
            String sql = String.Format("insert into QUABIEU_DIPLE (MaDipLe,MaQuaBieu ,NamDL,SoLuong,TriGiaQB,GhiChu_QBDL )" + "values(" + "'" + MaDipLe + "'" + "," + "'" + MaQuaBieu + "'" + ",convert(datetime," + "'" + NamDL + "'" + ", 103)," + "'" + SoLuong + "'" + "," + "'" + TriGiaQB + "'" + "," + "'" + GhiChuKHQB + "'" + ")");

            //MessageBox.Show(sql);
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại insert vào bảng QuaBieu_Diple");
                return;
            }

        }
        //Insert QuaBieu_DipLe
        public void InsertIntoQuaBieuDipLe(int MaDipLe, int MaQuaBieu, string NamDL, int SoLuong, int TriGiaQB, string GhiChuKHQB)
        {
            String sql = String.Format("Insert into QUABIEU_DIPLE (MaDipLe,MaQuaBieu ,NamDL,SoLuong,TriGiaQB,GhiChu_QBDL )" + "values(" + "'" + MaDipLe + "'" + "," + "'" + MaQuaBieu + "'" + ",convert(datetime," + "'" + NamDL + "'" + ", 103)," + "'" + SoLuong + "'" + "," + "'" + TriGiaQB + "'" + "," + "'" + GhiChuKHQB + "'" + ")");
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại insert vào bảng QuaBieu_Diple");
                return;
            }
        }
        //Insert table khachhangnhanquabieu
        public void LuuKhachHangNhanQuaBieu(int MaKH,int MaQBDL,string GhiChuKHQB)
        {
            String sql = String.Format("declare @MaQB_DL int =(select MAX( MaQB_DL ) from QUABIEU_DIPLE) Insert into KhachHangQuaBieu(MaKH,MaQB_DL,GhiChu) values ('" + MaKH + "',@MaQB_DL," + "N'" + GhiChuKHQB + "')");

            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại insert vào bảng KhachHangQuaBieu");
                return;
            }
        }
        //Update table khachhangquabieu
        public void UpdateKhachHangNhanQuaBieu(int ID)
        {
            String sql = String.Format("declare @MaQB_DL int =(select MAX( MaQB_DL ) from QUABIEU_DIPLE) Update KhachHangQuaBieu set MaQB_DL=@MaQB_DL where ID='" + ID + "'");

            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại insert vào bảng KhachHangQuaBieu");
                return;
            }
        }
        //Update table QuaBieuDipLe
        public void UpdateQuaBieuDiple(int MaQBDL, int MaDipLe, int MaQuaBieu, string NamDL, int SoLuong, double TriGiaQB)
        {
            String sql = "UPDATE QUABIEU_DIPLE SET MaDipLe='" + MaDipLe + "', MaQuaBieu='" + MaQuaBieu + "',NamDL=convert(datetime," + "'" + NamDL + "'" + ", 103),SoLuong='" + SoLuong + "',TriGiaQB='" + TriGiaQB + "' WHERE MaQB_DL='" + MaQBDL + "'";
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Cập nhật quà biếu khách hàng thất bại.");
                return;
            }
        }
        //Load data KH duoc nhan qua bieu
        public DataTable LayDataKHNhanQuaBieu(int Top)
        {
            //String sql = "select KHACHHANG.TENKH as N'Tên Khách Hàng',DIPLE.TenDipLe as N'Tên Dịp Lễ',QUABIEU.TenQuaBieu as N'Tên Quà Biếu', QUABIEU_DIPLE .SoLuong as N'Số Lượng',QUABIEU_DIPLE.TriGiaQB as N'Trị Giá',QUABIEU_DIPLE.NamDL as N'Ngày Dịp Lễ', QUABIEU_DIPLE .GhiChu_QBDL as N'Ghi chú' from QUABIEU_DIPLE inner join DIPLE  on QUABIEU_DIPLE .MaDipLe =DIPLE .MaDipLe inner join QUABIEU  on QUABIEU_DIPLE .MaQuaBieu = QUABIEU.MaQuaBieu inner join KhachHangQuaBieu on QUABIEU_DIPLE .MaQB_DL =KhachHangQuaBieu .MaQB_DL inner join KHACHHANG  on KhachHangQuaBieu .MaKH =KHACHHANG.MAKH ";
            String sql = "select top " + Top + " KhachHangQuaBieu.ID as ID,KHACHHANG.TENKH as N'Tên Khách Hàng',QUABIEU_DIPLE.MaQB_DL as N'Mã QBDL',DIPLE.TenDipLe as N'Tên Dịp Lễ',QUABIEU.TenQuaBieu as N'Tên Quà Biếu', QUABIEU_DIPLE .SoLuong as N'Số Lượng',QUABIEU_DIPLE.TriGiaQB as N'Trị Giá',(QUABIEU_DIPLE .SoLuong*QUABIEU_DIPLE.TriGiaQB)as N'Thành Tiền',QUABIEU_DIPLE.NamDL as N'Ngày Dịp Lễ', QUABIEU_DIPLE .GhiChu_QBDL as N'Ghi chú' from QUABIEU_DIPLE inner join DIPLE  on QUABIEU_DIPLE .MaDipLe =DIPLE .MaDipLe inner join QUABIEU  on QUABIEU_DIPLE .MaQuaBieu = QUABIEU.MaQuaBieu inner join KhachHangQuaBieu on QUABIEU_DIPLE .MaQB_DL =KhachHangQuaBieu .MaQB_DL inner join KHACHHANG  on KhachHangQuaBieu .MaKH =KHACHHANG.MAKH ORDER BY ID DESC";
            return mc.GetDataTable(sql);
        }
        //Inserts bang MaQB_DL & KhachHangQuaBieu
        #region Cau lenh sql 2
        // Form tìm kiếm 

        #endregion

        #region Cau lenh sql 3
        //Get list Makv,Tenkv From khuvuc 
        public DataTable getlistArea()
        {
            String sql = "select Makhuvuc,Tenkhuvuc from khuvuc";
            return mc.GetDataTable(sql);
        }
        //Get list MaSP,TenSP From SANPHAM 
        public DataTable getlistProduct()
        {
            String sql = "select Masp,Tensp from sanpham";
            return mc.GetDataTable(sql);
        }
         //Get list Manv,Tennv From Nhanvien
        public DataTable getlistEmployee()
        {
            String sql = "select Manhanvien,Tennhanvien from nhanvien";
            return mc.GetDataTable(sql);
        }
        public DataSet getDMKhachHang(string strsql,string table)
        {
            return mc.ExectueQueryDatasetreport(strsql,table,CommandType.Text, null);
        }
        #endregion

        #region Cau lenh sql 4

        #region form Chi phí khác
        //Thêm dòng dữ liệu vào table [NHANVIEN_CHIPHIDD]
        public void insertChiPhiKhac1(int macp, int manv, DateTime ngaycp, double giacp, string ghichucp)
        {
            //@machiphi,@manhanvien,@ngaychiphi,@giachiphi,@sott_nv_cpdd
            if (mc.ExecuteQuery("pro_insertchiphididuong", new SqlParameter("@machiphi", macp), new SqlParameter("@manhanvien", manv), new SqlParameter("@ngaychiphi", ngaycp), new SqlParameter("@giachiphi", giacp),new SqlParameter("@sott_nv_cpdd",ghichucp)))
            {
                //success
            }
            else
            {
                MessageBox.Show("Thêm hất bại. Kiểm tra lại.");
                return;
            }
        }
        public void insertChiPhiKhac(int macp, int manv, DateTime ngaycp, double giacp, string ghichucp)
        {
            string Isql = string.Format("INSERT INTO [QuanLyBanHang].[dbo].[NHANVIEN_CHIPHIDD]([MaChiPhi],[MaNhanVien],[NgayChiPhi],[GiaChiPhi],[GhiChuChiPhi]) " +
                            " Values('{0}', '{1}',{2}, '{3}', '{4}')", macp, manv, ngaycp, giacp, ghichucp);
            //convert(varchar, getdate(),101)

            // string Usql = string.Format("UPDATE [QuanLyBanHang].[dbo].[NHANVIEN_CHIPHIDD] SET [GiaChiPhi] = {0} ,[GhiChuChiPhi] = '{1}' WHERE sott_nv_cpdd={}", giacp, ghichucp, stt);
            if (mc.ExecuteQuery(Isql))
            {
                //success
            }
            else
            {
                MessageBox.Show("Thêm hất bại. Kiểm tra lại.");
                return;
            }
        }
        ////Update 1 dòng vào csdl
        public void UpdateChiPhiKhac(double giacp, string ghichucp, int stt)
        {
             string Usql = string.Format(" UPDATE [QuanLyBanHang].[dbo].[NHANVIEN_CHIPHIDD] " +
                            " SET [GiaChiPhi] = {0}" +
                            " ,[GhiChuChiPhi] = '{1}' "+
                            " WHERE sott_nv_cpdd={2} ",giacp,ghichucp,stt);
            if (mc.ExecuteQuery(Usql))
            {
                //success
            }
            else
            {
                MessageBox.Show("Sửa thất bại. Kiểm tra lại.");
                return;
            }
        }
        //Xóa dòng dữ liệu trong CSDl 
        public void DeleteChiPhiKhac(int sttcpdd)
        {
             string Dsql = string.Format("DELETE FROM [QuanLyBanHang].[dbo].[NHANVIEN_CHIPHIDD] " +
                            " WHERE SoTT_NV_CPDD={0}", sttcpdd);

             if (mc.ExecuteQuery(Dsql))
            {
                //success
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }
        }
        //Lấy danh sách chi phí đi đường theo ngày
        public DataTable GetChiPhiDD_TheoNgay(String ngaycp)
        {
            //String sql = "select MaKH, TenKH, TenCuaHang, DiaChiKH from KhachHang where makhuvuc=" + makhuvuc + " order by TenCuaHang";
            string sql = "select  kq.SoTT_NV_CPDD as sttdd, kq.ngaycp, kq.tencp,kq.giacp, (tb3.honhanvien + ' '+ tb3.tennhanvien) as ht,kq.manhanvien as kqmanv,kq.machiphi as kqmacp,kq.ghichuchiphi  " +
                                     " from nhanvien as tb3 inner join " +
                                     " (select tb1.ngaychiphi as ngaycp, tb2.tenchiphi as tencp, tb1.giachiphi as giacp, tb1.manhanvien, tb1.SoTT_NV_CPDD,tb1.ghichuchiphi,tb1.machiphi " +
                                            " from nhanvien_chiphidd as tb1 inner join chiphididuong as tb2 on tb1.machiphi=tb2.machiphi) as kq " +
                                     " on tb3.manhanvien=kq.manhanvien " +
                                     " where convert(datetime,ngaycp,103) = convert(datetime,'" + ngaycp + "' ,103 )  order by ngaycp desc ";

            return mc.GetDataTable(sql);
        }
        #endregion 

        #region form Doanh thu
        //Lấy tổng doanh thu bán hàng theo ngày. Tức là tổng số tiền THANH TOÁN khách hàng hay Tổng thanh toán thưc tế
        public double LayTongThanhToan_BC(String ngaybc)
        {
            String getsql = String.Format("select sum(sotienthanhtoan)  " +
                        " from khachhang_thanhtoan " +
                        " where convert(datetime,ngaythanhtoan) = convert(datetime,'{0}' )",ngaybc);
            return mc.TraveDongDuLieu(getsql);
        }
        //Lấy tổng chi phí đi đường theo ngày.
        public double LayTongChiPhiDiDuong_BC(String ngaybc)
        {
            String getsql = String.Format(" select sum(giachiphi) from nhanvien_chiphidd " +
                                            " where convert(datetime,ngaychiphi) = convert(datetime,'{0}' )", ngaybc);
            return mc.TraveDongDuLieu(getsql);
        }

        //public double TongTienKhachNoNgayMuaMoiNhat(int MaKH)
        //{
        //    String sql = "select SUM(thanhtien) from KH_MUA_SP where MAKH=" + MaKH + " and NGAYMUA=(select max(NGAYMUA) from KH_MUA_SP where MAKH=" + MaKH + ") Group by MaKH, NgayMua";
        //    return mc.TraveDongDuLieu(sql);
        //}

        #endregion
        #endregion

        #region Tổng các loại Tiền 28/12/2012

        public double TongTienKH_MuaSP_theoThang(KhachHangMuaSP kh) //Tính tổng tiền Khách hàng mua sản phẩm theo tháng hiện tại
        {
            String sql = "select sum(ThanhTien) from KH_Mua_SP where MaKH='" + kh.MaKH + "' and month(ngaymua)='"+ kh.Thang + "' and year(ngaymua)='"+ kh.Nam +"'";
            return mc.TraveDongDuLieu(sql);
        }
        public double TongTienKH_ThanhToan_theoThang(KhachHangMuaSP kh) //Tính tổng tiền Khách hàng Thanh toán theo tháng hiện tại
        {
            String sql = "select sum(SoTienThanhToan) from KhachHang_ThanhToan where MaKH='" + kh.MaKH + "' and month(ngaythanhtoan)='" + kh.Thang + "' and year(ngaythanhtoan)='" + kh.Nam + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public double TongTienKH_CongNo_theoThang(KhachHangMuaSP kh) //Tính tổng tiền công nợ = tổng mua hàng - tổng thanh toán
        {
            String sql = "select (select TongTienMuaSP from TONGKHMUASP where MaKH='"+kh.MaKH+ "' and year(NamTongTienMuaSP)='"+ kh.Nam + "' and month(NamTongTienMuaSP)='"+ kh.Thang + "')-(select TongThanhToan from TONGKHTHANHTOAN where MaKH='"+ kh.MaKH +"' and year(NamTongThanhToan)='"+ kh.Nam +"' and month(NamTongThanhToan)='"+kh.Thang+"')";
            return mc.TraveDongDuLieu(sql);
        }
        public double TongTienKH_CongNo_theoThangTruoc(KhachHangMuaSP kh) //Tính tổng tiền công nợ tháng trước
        {
            String sql = "select TongCongNo from TONGKHCONGNO where MaKH='"+ kh.MaKH +"' and year(NamTongCongNo)='"+kh.Namtruoc +"' and month(NamTongCongNo)='"+kh.Thangtruoc +"'";
            return mc.TraveDongDuLieu(sql);
        }
        //////////Kiểm tra dữ liệu có chưa trước khi insert hay update
        public double KiemTraTongTienKH_MuaSP_theoThang(KhachHangMuaSP kh)//Kiểm tra nếu có dữ liệu trả về 1, o là chưa có
        {
            String sql = "select count(makh) from TONGKHMUASP where MaKH=" + kh.MaKH + " and year(NamTongTienMuaSP)='"+kh.Nam + "' and month(NamTongTienMuaSP)='"+kh.Thang+"'";
            return mc.TraveDongDuLieu(sql);
        }
        public double KiemTraTongTienKH_ThanhToan_theoThang(KhachHangMuaSP kh)//Kiểm tra nếu có dữ liệu trả về 1, o là chưa có
        {
            String sql = "select count(makh) from TONGKHTHANHTOAN where MaKH=" + kh.MaKH + " and year(NamTongThanhToan)='" + kh.Nam + "' and month(NamTongThanhToan)='" + kh.Thang + "'";
            return mc.TraveDongDuLieu(sql);
        }
        public double KiemTraTongTienKH_CongNo_theoThang(KhachHangMuaSP kh)//Kiểm tra nếu có dữ liệu trả về 1, o là chưa có
        {
            String sql = "select count(makh) from TONGKHCONGNO where MaKH='"+kh.MaKH +"' and year(NamTongCongNo)='"+ kh.Nam + "' and month(NamTongCongNo)='"+kh.Thang +"'";
            return mc.TraveDongDuLieu(sql);
        }
        public double KiemTraTongTienKH_CongNo_theoThangtruoc(KhachHangMuaSP kh)//Kiểm tra nếu có dữ liệu trả về 1, o là chưa có
        {
            String sql = "select count(makh) from TONGKHCONGNO where MaKH='" + kh.MaKH + "' and year(NamTongCongNo)='" + kh.Namtruoc + "' and month(NamTongCongNo)='" + kh.Thangtruoc + "'";
            return mc.TraveDongDuLieu(sql);
        }
        //////////Kết thúc việc kiểm tra insert hay update

        //////////INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT /////
        public void InsertTongTienKH_MuaSP_theoThang(KhachHangMuaSP kh)// Hàm lưu lại tổng số tiền khách Mua Sản phẩm
        {
            String sql = String.Format("Insert into TONGKHMUASP(MaKH, NamTongTienMuaSP, TongTienMuaSP)"
                + " Values('{0}', '{1}', '{2}')", kh.MaKH, kh.NgayMua, kh.ThanhTien);
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }

        }
        public void InsertTongTienKH_ThanhToan_theoThang(KhachHangMuaSP kh)// Hàm lưu lại số tiền khách Thanh toán
        {
            String sql = String.Format("Insert into TONGKHTHANHTOAN(MaKH, NamTongThanhToan, TongThanhToan)"
                + " Values('{0}', '{1}', '{2}')", kh.MaKH, kh.NgayThanhToan, kh.SoTienThanhToan);
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }

        }
        public void InsertTongTienKH_CongNo_theoThang(KhachHangMuaSP kh)// Hàm lưu lại số tiền khách nợ 
        {
            String sql = String.Format("Insert into TONGKHCONGNO(MaKH, NamTongCongNo, TongCongNo)"
                + " Values('{0}', '{1}', '{2}')", kh.MaKH, kh.NgayMua, kh.ThanhTien);
            if (mc.ExecuteQuery(sql))
            {
                //Success is OK not message
            }
            else
            {
                MessageBox.Show("Thất bại");
                return;
            }

        }
        ////////END INSERT     END INSERT     END INSERT     END INSERT     END INSERT     END INSERT     END INSERT     


        ////////UPDATE   UPDATE   UPDATE   UPDATE   UPDATE   UPDATE   UPDATE   UPDATE   UPDATE   UPDATE   
        public bool UpdateTongTienKH_MuaSP_theoThang(KhachHangMuaSP kh)//Cập nhật 
        {
            String sql = String.Format("Update TONGKHMUASP Set TongTienMuaSP='{0}' where MaKH='{1}' and year(NamTongTienMuaSP)='{2}' and month(NamTongTienMuaSP)='{3}'", kh.ThanhTien, kh.MaKH,kh.Nam, kh.Thang);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;
        }
        public bool UpdateTongTienKH_ThanhToan_theoThang(KhachHangMuaSP kh)//Cập nhật
        {
            String sql = String.Format("Update TONGKHTHANHTOAN Set TongThanhToan='{0}' where MaKH='{1}' and year(NamTongThanhToan)='{2}' and month(NamTongThanhToan)='{3}'", kh.ThanhTien, kh.MaKH, kh.Nam, kh.Thang);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;
        }
        public bool UpdateTongTienKH_CongNo_theoThang(KhachHangMuaSP kh)//Cập nhật
        {
            String sql = String.Format("Update TONGKHCONGNO Set TongCongNo='{0}' where MaKH='{1}' and year(NamTongCongNo)='{2}' and month(NamTongCongNo)='{3}'", kh.ThanhTien, kh.MaKH, kh.Nam, kh.Thang);
            if (mc.ExecuteQueryUpdate(sql))
                return true;
            return false;
        }
        //////END UPDATE    END UPDATE    END UPDATE    END UPDATE    END UPDATE    END UPDATE    END UPDATE    

        #endregion

        #region Form tìm kiếm
        //Tim kiếm khách hàng
        public DataTable TimKiemKhachHangMuaSanPham(String TenKhachHang)
        {
            String sql = "select tencuahang,ngaymua, tensp, soluongmua,(gia_sp_ngaymua + gia_chietkhau_ngaymua) as dongia, thanhtien,sotienthanhtoan, ghichu_khmsp" +
                    " from kh_mua_sp t1, khachhang t2, sanpham t3, khachhang_thanhtoan t4" +
                    " where t1.makh=t2.makh and t1.masp=t3.masp  and" +
                    " t1.ngaymua=t4.ngaythanhtoan and t1.ghichu_khmsp <> 'default'" +
                    " and t1.makh=t4.makh and t2.tencuahang like N'"+ TenKhachHang+ "'";
            return mc.GetDataTable(sql);
        }
        //Tìm kiếm thông tin khách hàng
        public DataTable TimKieThongTinKH(String TenKhachHang)
        {
            string sql = "select tenkh, tencuahang,diachikh, dienthoaikh from " +
            " khachhang where tencuahang like N'" + TenKhachHang + "' and tinhtrangkh=1";
            return mc.GetDataTable(sql);
        }
        #endregion
    }
}
